#！D:\MyWork\Program\python
# -*- coding: utf-8 -*-
import pymysql

if __name__ == '__main__':
    conn = pymysql.Connect(host = '10.170.128.56',
                           port = 3306,
                           user = 'root',
                           passwd = '123456',
                           db = 'books',
                           charset='utf8mb4')
    cur = conn.cursor()
    tables=['books_1100','books_700','books_early_300']

    #sql="select book_id from books_1500_1900 where `国籍`='中国' and `书名`  like '%先醒斋笔记 %' group by book_id order by id "
    sql="select book_id from books_1500_1900 where `国籍`='中国' and `书名` not like '%S%' and `书名` not like '%M%' and `书名` not like '%(1)%'  group by book_id order by id"
    cur.execute(sql)
    result=cur.fetchall()
    print('共有书籍：'+str(len(result))+'册')
    for book_id in result:
        try:
            print('book_id: '+book_id[0])
            info="select * from books_1500_1900 where book_id='"+book_id[0]+"'"
            cur.execute(info)
            datas=cur.fetchall()#datas同本书的多条章节数据
            #print(datas)
            section=[]
            context=[]
            for data in datas:
                name=data[1]
                author=data[3]
                abstract=data[2]
                createtime=data[5]
                historytime=data[7]
                source=data[8]
                sourcetitle=data[1]
                section.append(data[10])
                context.append(data[11])
                index=data[13]
            #r=open("E:\\work\\ancient_books\\books\\"+name.replace(':','_').replace('.','').replace(' ','')+".xml","a",encoding="utf-8")
            r=open("E:\\work\\ancient_books\\books\\"+"book_"+book_id[0]+"_"+str(index)+".xml","a",encoding="utf-8")
            r.write(r'<?xml version="1.0" encoding="utf-8" standalone="no"?>'+'\n')
            r.write(r'<article id="6" type="journal" filename="NFLJ202005004">'+'\n')
            r.write(r'<title>'+name+'</title>'+'\n')
            r.write(r'<prolog>'+'\n')
            r.write(r'<authormeta>'+'\n')
            authors=author.split(' ')
            if len(authors)>1:
                for i in range(len(authors)):
                    r.write('\t'+r'<personinfo id="'+str(i+1)+'">'+'\n')
                    r.write('\t\t'+r'<fullname>'+authors[i]+'</fullname>'+'\n')
                    r.write('\t'+r'</personinfo>'+'\n')
            else:
                r.write('\t'+r'<personinfo id="1">'+'\n')
                r.write('\t\t'+r'<fullname>'+authors[0]+'</fullname>'+'\n')
                r.write('\t'+r'</personinfo>'+'\n')
            r.write(r'</authormeta>'+'\n')
            r.write(r'<sourcemeta>'+'\n')
            r.write('\t'+r'<source>'+source+'</source>'+'\n')
            r.write('\t'+r'<sourcetitle>'+sourcetitle+'</sourcetitle>'+'\n')
            r.write('\t'+r'<cn></cn><issn></issn><year></year><issue>'+'\n')
            r.write('\t'+r'</issue><volume></volume>'+'\n')
            r.write('\t'+r'<articleloc><pagenum></pagenum><page></page></articleloc>'+'\n')
            r.write('\t'+r'<articletype></articletype><category></category>'+'\n')
            r.write(r'</sourcemeta>'+'\n')
            r.write(r'<timemeta>'+'\n')
            r.write('\t'+r'<createtime>'+createtime+'</createtime>'+'\n')
            r.write('\t'+r'<historytime>'+historytime+'AD'+'</historytime>'+'\n')
            r.write(r'</timemeta>'+'\n')
            r.write(r'<keywords><keyword></keyword></keywords>'+'\n')
            r.write(r'</prolog>'+'\n')
            r.write(r'<abstract>'+abstract+'</abstract>'+'\n')
            r.write(r'<fullparagraph>'+'\n')
            r.write(r'</fullparagraph>'+'\n')
            r.write(r'<body>'+'\n')
            #如果有详情页
            id=1
            for sec in range(len(section)):
                if section[sec]:
                    r.write(r'<section id="'+str(id)+'" level="1" index="'+str(sec)+'">'+'\n')
                    id=id+1
                    r.write(r'<title>'+section[sec]+'</title>'+'\n')
                    lines=context[sec].split('\n')#获取内容中的所有行
                    for line in lines:
                        r.write(r'<p id="'+str(id)+'">'+line+'</p>'+'\n')
                        id=id+1
                    r.write(r'</section>'+'\n')
                else:
                    r.write(r'<section id="1" level="1" index="'+str(sec)+'">'+'\n')
                    r.write(r'<title></title>'+'\n')
                    r.write(r'</section>'+'\n')

            r.write(r'</body>'+'\n')
            r.write(r'<bibliography>'+'\n')
            r.write(r'<biblioitem id="'+str(id)+'">'+'\n')
            id=id+1
            r.write(r'<title></title>'+'\n')
            r.write(r'<title xml:lang="en"></title>'+'\n')
            r.write(r'</biblioitem>'+'\n')
            r.write(r'</bibliography>'+'\n')
            r.write(r'</article>'+'\n')
            r.close()
        except Exception:
            w=open("E:\\work\\ancient_books\\转xml失败.txt","a",encoding="utf-8")
            w.write(book_id[0]+'\n')
            w.close()
    cur.close()
    conn.close()